package dao;

import bean.Student;
import util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAO {

    public int getTotal() {

        int total = 0;

        String sql = "SELECT COUNT(*) FROM student";
        try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) {

            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return total;
    }

    public void add(Student student) {

        String sql = "INSERT INTO student VALUES(NULL,?,?,?,?)";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1,student.getStudentID());
            ps.setString(2,student.getName());
            ps.setInt(3,student.getAge());
            ps.setString(4,student.getSex());

            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(int id) {

        String sql = "DELETE FROM student WHERE ID = ?";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1, id);

            ps.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void update(Student student) {

        String sql = "update student set studentId = ?, name = ?, age = ?, sex = ? where id = ? ";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1, student.getStudentID());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getAge());
            ps.setString(4, student.getSex());
            ps.setInt(5, student.getId());

            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Student get(int id) {

        Student student = new Student();

        String sql = "SELECT * FROM student WHERE ID = " + id;
        try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) {

            ResultSet rs = st.executeQuery(sql);

            if (rs.next()) {
                int student_id = rs.getInt("studentId");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String sex = rs.getString("sex");
                student.setStudentID(student_id);
                student.setName(name);
                student.setAge(age);
                student.setSex(sex);
                student.setId(id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return student;
    }

    public ArrayList<Student> getStudent(String type, String content) {
        String sql = null;;
        if (type.equals("sno")){
            sql = "SELECT * FROM student WHERE studentId = " + content;
        }else if(type.equals("sname")){
            sql = "SELECT * FROM student WHERE name = '"+content+"'";//注意中文查询要加引号
        }else return null;

        ArrayList<Student> students = new ArrayList<>();
        try (Connection c = DBUtil.getConnection(); Statement st = c.createStatement()) {
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()) {
                Student student = new Student();
                int student_id = rs.getInt("studentID");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String sex = rs.getString("sex");
                student.setStudentID(student_id);
                student.setName(name);
                student.setAge(age);
                student.setSex(sex);
                student.setId(rs.getInt("id"));
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return students;
    }

    public List<Student> list() {
        return list(0, Short.MAX_VALUE);
    }

    public List<Student> list(int start, int count) {

        List<Student> students = new ArrayList<>();

        String sql = "SELECT * FROM student ORDER BY studentId desc limit ?,?";

        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {

            ps.setInt(1, start);
            ps.setInt(2, count);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Student student = new Student();
                int id = rs.getInt("id");
                int studentID = rs.getInt("studentId");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String sex = rs.getString("sex");

                student.setId(id);
                student.setStudentID(studentID);
                student.setName(name);
                student.setAge(age);
                student.setSex(sex);

                students.add(student);
            }

            rs.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return students;
    }
    public static void main(String[] args){
        StudentDAO studentDAO = new StudentDAO();
        List<Student> students = studentDAO.list();
        System.out.println(students.get(0).getName());
    }
}